{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3a43de1a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "file_path = r\"C:\\Users\\Draw the sword\\Desktop\\商业数据分析\\《Power BI商业数据分析项目实战》\\第3篇 销售案例6 7 8 9\\第9章\\数据源.xlsx\"\n",
    "df_sales= pd.read_excel(file_path, sheet_name='销售明细', engine='openpyxl')\n",
    "df_traffic=pd.read_excel(file_path, sheet_name='客流数据', engine='openpyxl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "89a48871",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "销售零售额: 488080.3\n",
      "业绩: 486685.4\n",
      "销售数量: 1393\n",
      "成交笔数: 856\n",
      "件单价: 349.37932519741565\n",
      "客单价: 568.5577102803738\n",
      "客单量: 1.6273364485981308\n",
      "客流数: 8183\n",
      "成交率: 0.10460711230600024\n",
      "销售折扣: 0.99714206863092\n",
      "平均零售价: 350.38068916008615\n"
     ]
    }
   ],
   "source": [
    "# 计算销售零售额\n",
    "销售零售额 = (df_sales['销量'] * df_sales['销售额']).sum()\n",
    "\n",
    "# 计算业绩\n",
    "业绩 = df_sales['销售额'].sum()\n",
    "\n",
    "# 计算销售数量\n",
    "销售数量 = df_sales['销量'].sum()\n",
    "\n",
    "# 计算成交笔数\n",
    "成交笔数 = df_sales['销售单编号'].nunique()\n",
    "\n",
    "# 计算件单价\n",
    "件单价 = 业绩 / 销售数量\n",
    "\n",
    "# 计算客单价\n",
    "客单价 = 业绩 / 成交笔数\n",
    "\n",
    "# 计算客单量\n",
    "客单量 = 销售数量 / 成交笔数\n",
    "\n",
    "# 计算客流数\n",
    "客流数 = df_traffic['客流数量'].sum()\n",
    "\n",
    "# 计算成交率\n",
    "成交率 = 成交笔数 / 客流数\n",
    "\n",
    "# 计算销售折扣\n",
    "销售折扣 = 业绩 / 销售零售额\n",
    "\n",
    "# 计算平均零售价\n",
    "平均零售价 = 销售零售额 / 销售数量\n",
    "\n",
    "# 将结果存储在一个字典中\n",
    "results = {\n",
    "    '销售零售额': 销售零售额,\n",
    "    '业绩': 业绩,\n",
    "    '销售数量': 销售数量,\n",
    "    '成交笔数': 成交笔数,\n",
    "    '件单价': 件单价,\n",
    "    '客单价': 客单价,\n",
    "    '客单量': 客单量,\n",
    "    '客流数': 客流数,\n",
    "    '成交率': 成交率,\n",
    "    '销售折扣': 销售折扣,\n",
    "    '平均零售价': 平均零售价\n",
    "}\n",
    "\n",
    "# 显示结果\n",
    "for key, value in results.items():\n",
    "    print(f\"{key}: {value}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7f6edc08",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = r\"C:\\Users\\Draw the sword\\Desktop\\商业数据分析\\《Power BI商业数据分析项目实战》\\第3篇 销售案例6 7 8 9\\第8章\\数据源.xlsx\"\n",
    "df_2= pd.read_excel(file_path, sheet_name='销售明细', engine='openpyxl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c878dccc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "成交笔数_假设: 3577.0\n"
     ]
    }
   ],
   "source": [
    "# 假设的成交率值\n",
    "成交率_假设_值 = 0.15  \n",
    "\n",
    "# 计算客流数\n",
    "客流数 = df_2['销量'].sum()\n",
    "\n",
    "# 计算成交笔数_假设\n",
    "成交笔数_假设 = round(客流数 * 成交率_假设_值, 0)\n",
    "\n",
    "# 显示结果\n",
    "print(f\"成交笔数_假设: {成交笔数_假设}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "36be1ec7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0        136\n",
      "1        156\n",
      "2         60\n",
      "3        158\n",
      "4         47\n",
      "        ... \n",
      "23031     92\n",
      "23032     59\n",
      "23033    163\n",
      "23034     85\n",
      "23035     41\n",
      "Length: 23036, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df_2['件单价'] = np.random.randint(1, 201, size=len(df_2))\n",
    "客单价_假设 = df_2['件单价'] * df_2['销量']\n",
    "##将客单价假设加入数据\n",
    "print(客单价_假设)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1892dfb1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0          0\n",
      "1         36\n",
      "2          0\n",
      "3         11\n",
      "4          4\n",
      "        ... \n",
      "23031      1\n",
      "23032      0\n",
      "23033    152\n",
      "23034     17\n",
      "23035      0\n",
      "Length: 23036, dtype: int32\n"
     ]
    }
   ],
   "source": [
    "客单价_假设 = 150.0  # 例如，假设客单价为 150.0\n",
    "成交笔数_假设 = 1000  # 例如，假设成交笔数为 1000\n",
    "\n",
    "# 计算业绩_假设\n",
    "df_2['件单价'] = np.random.randint(1, 201, size=len(df_2))\n",
    "客单价_假设 = df_2['件单价'] * df_2['销量']\n",
    "df_2['客单价_假设'] = np.random.randint(客单价_假设)\n",
    "df_2['成交笔数_假设 '] = np.random.randint(0,2,size=len(df_2))\n",
    "业绩_假设 = df_2['客单价_假设'] * df_2['成交笔数_假设 ']\n",
    "print(业绩_假设)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
